package com.globalwave.common.db;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Column;



import org.apache.commons.beanutils.BeanUtils;
import org.hibernate.SessionFactory;

import com.globalwave.base.BaseSO;
import com.globalwave.common.ArrayOrderList;
import com.globalwave.common.ArrayPageList;
import com.globalwave.common.C;
import com.globalwave.common.cache.CodeHelper;
import com.globalwave.common.db.gen.SQLGenerator;
import com.globalwave.common.db.gen.SqlGeneratorAdapter;
import com.globalwave.common.exception.BusinessException;

/**
 * 用于使用对象方式，进行数据的持久化操作
 * 
 * @author Virgil.Chen
 *
 */
public class SimpleJDBCDAO extends BaseJDBCDAO {
	
    private SqlGeneratorAdapter generatorAdapter = new SqlGeneratorAdapter(this) ;
    private Connection connection ;
    
    private static Map<Class<?>, Integer> typesMap = new HashMap<Class<?>, Integer>() ;
    static {
    	typesMap.put(Short.class, Types.SMALLINT) ;
    	typesMap.put(Integer.class, Types.INTEGER) ;
    	typesMap.put(Long.class, Types.BIGINT) ;
    }
    
    private SessionFactory sessionFactory ;
    
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory ;   
    }
    
    public Connection getConnection() {
    	//if (connection == null) {
    	if (sessionFactory != null) {
    		connection = sessionFactory.getCurrentSession().connection() ;
    	}
    	//}
        return connection; 

    }
    
    public void setConnection(Connection connection) {
    	this.connection = connection ; 
    }


    /**
     * 插入一个对象
     * 
     * @param object 需要持久化的对象
     * @return
     * @throws Exception
     */
    public Object insert(Object object) throws Exception {
        return this.insert(object, false) ;
    }


    /**
     * 插入多个同类型的对象，主要是为了提高性能
     * 
     * @param objects 同类型的对象
     * @throws Exception
     */
    public void insertAll(List<?> objects) throws Exception {
    	if (objects == null || objects.size() == 0) {
    		return ;
    	}
        for (Object obj:objects) {
        	insert(obj) ;// TODO 需要改为批量操作，提高效率！！
        }
    }

    /**
     * 插入一个对象，可以是包含文件对象
     * 
     * @param object 需要持久化的对象
     * @param isFileObject 是不包含文件对象
     * @return
     */
    public Object insert(Object object, final boolean isFileObject) {
    	
    	SQLGenerator.INSERT insert = null ;
    	SQLGenerator generator = null ;
    	
    	try {
    		generator = generatorAdapter.getInstance(object) ;
	        insert = generator.getInsert() ;       
	        
	        execute(insert.sql.toString(), insert.params.toArray()) ;
	        
    	} catch (BusinessException e) {
    		throw e;
    	} catch (Exception e) {
			log.error(e, e) ;
    		throw new BusinessException(e, "1000") ;
    	} 
    	
        return object;
    }


    /**
     * 更新一个对象中的所有属性
     * 
     * @param object 需要更新的对象，可以是Map对象，更为方便
     * @return
     * @throws Exception
     */
    public int update(Object object) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        return this.update(generator, generator.getIdWhere()) ;
        
    } 
    
    /**
     * 指定条件，更新所有对象的所有属性
     * 
     * @param object 对象更新值（包括需要更新的表信息）
     * @param criterion 更新对象的条件
     * @return
     * @throws Exception
     */
    public int update(Object object, Object criterion) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
    	SQLGenerator.WHERE where = generator.getWhere() ;
    	
    	generator = generatorAdapter.getInstance(object) ;
    	
        return this.update(generator, where) ;
    } 
    
    private int update(SQLGenerator generator, SQLGenerator.WHERE where) throws Exception {
    	SQLGenerator.UPDATE update = null ;
    	Object[] params = null ;
    	int i = 0, size = 0 ;
    	
    	try {    				
        	update = generator.getUpdate() ;
        	
        	StringBuffer sql = update.sql.append(where.sql) ;
        	
        	params = new Object[update.params.size() + where.params.size()] ;
        	
        	size = update.params.size();
	        for( ; i < size ; i ++) {
	            params[i] = update.params.get(i) ;
	        }
	        
        	size = where.params.size() ;
	        for( int j = 0; j < size ; j ++) {
	        	params[j + i] = where.params.get(j) ;
	        }
	        
            return execute(sql.toString(), params) ;
            
    	} catch (BusinessException e) {
    		throw e;
    	} catch (Exception e) {
			log.error(e, e) ;
    		throw new BusinessException(e, "1000") ;
    	}
    }
    
    /**
     * 指定表，字段，条件进行更新
     * 
     * @param tableName
     * @param updateFields
     * @param updateValues
     * @param whereFields
     * @param whereValues
     * @return
     * @throws Exception
     */
    public int update(
    		String tableName, 
    		String[] updateFields, Object[] updateValues, 
    		String[] whereFields, Object[] whereValues) 
    throws Exception {
    	


        Map<String, Object> updateMap = new HashMap<String, Object>() ;
        for (int i = 0 ; i < updateFields.length ; i ++) {
            updateMap.put(updateFields[i], updateValues[i]) ;
        }
        updateMap.put(MapBean.KEY_TABLE, tableName) ;
        
        Map<String, Object> whereMap = new HashMap<String, Object>() ;
        for (int i = 0 ; i < whereFields.length ; i ++) {
        	whereMap.put(whereFields[i], whereValues[i]) ;
        }
        
        return update(updateMap, whereMap);
    }

    public int delete(Object object) throws Exception {
        //SQLGenerator generator = new SQLGenerator() ;
    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        //SQLGenerator.WHERE where = generator.getIdWhere() ;
        //String sql = " DELETE FROM " + generator.getTableName() + where.sql ;
        //return delete(sql, where);
        return delete(generator.getTableName(), generator, true);
        
    }

    public int delete(Class<?> clazz, Object criterion) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        //SQLGenerator.WHERE where = generator.getWhere() ;
        //String sql = " DELETE FROM " + generator.getTableName(clazz) + where.sql ;
        
        return delete(generator.getTableName(clazz), generator, false);
        
    }

    public int delete(String tableName, Object criterion) throws Exception {
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        //SQLGenerator.WHERE where = generator.getWhere() ;
        //String sql = " DELETE FROM " + tableName + where.sql ;
        
        return delete(tableName, generator, false);
        
    }

    private int delete(String tableName, SQLGenerator generator, boolean isById) throws SQLException {
    	
    	SQLGenerator.DELETE delete = null ;
    	
    	try {
    		delete = generator.getDelete(tableName, isById) ;
	
	        return execute(delete.sql.toString(), delete.params.toArray()) ;
	        
		} catch (BusinessException e) {
    		throw e;
    	} catch (Exception e) {
			log.error(e, e) ;
    		throw new BusinessException(e, "1000") ;
    	}
      
    }   
    
    /**
     * 根据对象条件，查找对象
     */
    public Object find(Object object) throws Exception {

    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        SQLGenerator.SELECT select = generator.getSelect() ;
        
        ArrayPageList<?> list = 
        		query(select.sql, select.params, ArrayPageList.PAGEINDEX_NO_PAGE, 0, object.getClass(), isFileObject) ;

        if (list.size() > 0) {
            return list.get(0) ;
        } else {
            return null ;
        }
        /*        
        ArrayPageList<?> list = query(object.getClass(), object, null, null, null) ;
        if (list.size() > 0) {
            return list.get(0) ;
        } else {
            return null ;
        }*/
    }
    
    public Object get(Object object) throws Exception {

    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        SQLGenerator.SELECT select = generator.getSelect(true) ;
        
        //String sql = "Select * from " + new SQLGenerator().getTableName(clazz);
        //return query(sql, clazz, criterion, orderList, pageIndex,pageSize) ;

        ArrayPageList<?> list = 
        		query(select.sql, select.params, ArrayPageList.PAGEINDEX_NO_PAGE, 0, object.getClass(), isFileObject) ;

        if (list.size() > 0) {
            return list.get(0) ;
        } else {
            return null ;
        }
        /*
        //SQLGenerator generator = new SQLGenerator() ;
    	SQLGenerator generator = generatorAdapter.getInstance(object) ;
        SQLGenerator.WHERE where = generator.getIdWhere() ;
        String sql = " Select * FROM " + generator.getTableName() + where.sql ;
        
        ArrayPageList<?> list = query(sql, where.params, null, null, object.getClass(), false);
        if (list.size() > 0) {
            return list.get(0) ;
        } else {
            return null ;
        }*/
    }
    
    /**
     * 主要为了方便执行count/max等sql，其它建议使用queryName，使用XML配置
     * 
     * @param sql
     * @param criterion
     * @param clazz
     * @param isFileObject
     * @return
     * @throws Exception
     */
    public Long getLong(
    		String tableName,
    		String colName,
    		Object criterion, 
    		Long defaultValue) throws Exception {

    	Long result = null ;
    	
    	if (criterion == null) {
    		result = getLong("select " + colName + " from " + tableName, null) ;
    	} else {
	    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
	        SQLGenerator.SELECT select = generator.getSelect(tableName, new String[]{colName}, false) ;
	        result = getLong(select.sql.toString(), select.params.toArray()) ;
    	}
    
        if (result == null) {
        	return defaultValue ;
        }
        
        return result ;
    }    
    
    public ArrayPageList<?> query(BaseSO so, Class<?> clazz) throws Exception {
    	ArrayOrderList orderList = so.getOrderList() ;
    	
    	int index = so.getPageIndex() == null?ArrayPageList.PAGEINDEX_NO_PAGE:so.getPageIndex();
    	int size = so.getPageSize() == null?0:so.getPageSize() ;

    	SQLGenerator generator = generatorAdapter.getInstance(so) ;
        SQLGenerator.SELECT select = generator.getSelect(generator.getTableName(clazz)) ;
        
        //sql = String.format("select * from (%s) a",sql) ;
        
        if (orderList != null) {
        	select.sql.append(orderList.toOrderString()) ;    
        }
        
        return query(select.sql, select.params, index, size, clazz, isFileObject) ;    	
    	//return query(so, index, size, orderList, clazz) ;
    }
    
    /**
     * 
     * @deprecated
     * 
     * pls use {@link #query(BaseSO, Class)}
     * 
     * @param criterion
     * @param orderList
     * @param clazz
     * @return
     * @throws Exception
     */
    public ArrayPageList<?> query(Object criterion, ArrayOrderList orderList, Class<?> clazz) throws Exception {
        return query(criterion, null, null, orderList, clazz) ;
    }

    /**
     * 
     * @deprecated
     * 
     * pls use {@link #query(BaseSO, Class)}
     * 
     * @param criterion
     * @param orderList
     * @param clazz
     * @return
     * @throws Exception
     */
    public ArrayPageList<?> query(
    		Object criterion, 
    		Integer pageIndex, 
    		Integer pageSize, 
    		ArrayOrderList orderList, 
    		Class<?> clazz) 
    throws Exception {

    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        SQLGenerator.SELECT select = generator.getSelect() ;
        
        //sql = String.format("select * from (%s) a",sql) ;
        
        if (orderList != null) {
        	select.sql.append(orderList.toOrderString()) ;    
        }
        
        //String sql = "Select * from " + new SQLGenerator().getTableName(clazz);
        //return query(sql, clazz, criterion, orderList, pageIndex,pageSize) ;

        return query(select.sql, select.params, pageIndex, pageSize, clazz, isFileObject) ;
    }
    
    /**
     * 
     * @deprecated
     * 
     * pls use {@link #query(BaseSO, Class)}
     * @param sql
     * @param criterion
     * @param orderList
     * @param clazz
     * @return
     * @throws Exception
     */
    public ArrayPageList<?> query( 
    		StringBuffer sql,
    		Object criterion,
    		ArrayOrderList orderList,
    		Class<?> clazz) throws Exception {
        return query(sql, criterion, null, null, orderList, clazz) ;    
    }
    
    /**
     * 
     * @deprecated
     * 
     * pls use {@link #query(BaseSO, Class)}
     * 
     * @param sql
     * @param criterion
     * @param pageIndex
     * @param pageSize
     * @param orderList
     * @param clazz
     * @return
     * @throws Exception
     */
    public ArrayPageList<?> query(
            StringBuffer sql,
            Object criterion, 
            Integer pageIndex, 
            Integer pageSize,
            ArrayOrderList orderList,
            Class<?> clazz) 
    throws Exception {
        
        return query(sql, criterion, pageIndex, pageSize, orderList, clazz, false) ; 
    }
    
    /**
     * 
     * @deprecated
     * 
     * pls use {@link #query(BaseSO, Class)}
     * 
     * @param sql
     * @param criterion
     * @param pageIndex
     * @param pageSize
     * @param orderList
     * @param clazz
     * @param isFileObject
     * @return
     * @throws Exception
     */
    public ArrayPageList<?> query(
    		StringBuffer sql,
    		Object criterion, 
    		Integer pageIndex, 
    		Integer pageSize,
    		ArrayOrderList orderList,
    		Class<?> clazz,  
    		final boolean isFileObject) 
    throws Exception {

    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        SQLGenerator.SELECT select = generator.getSelect(sql, false) ;
        
        //sql = String.format("select * from (%s) a",sql) ;
        
        if (orderList != null) {
            sql.append(orderList.toOrderString()) ;    
        }
        
        return query(sql, select.params, pageIndex, pageSize, clazz, isFileObject) ;        
    }


    

    private ArrayPageList<?> query(
    		StringBuffer sqlBuffer, 
    		final List<Object> params, 
    		int pageIndex, 
    		final int pageSize, 
    		final Class<?> clazz, 
    		final boolean isFileObject) {

        int fileFieldIndex = -1 ;
    	boolean clazzIsMap = (clazz.equals(HashMap.class)) ;
    	
    	String sql = sqlBuffer.toString() ;
    	
        ArrayPageList<Object> result = new ArrayPageList<Object>() ;

        if (ArrayPageList.PAGEINDEX_NO_PAGE != pageIndex) {
        	
            if (pageIndex != ArrayPageList.PAGEINDEX_NO_TOTAL_WITH_PAGE) {
                result.setTotal(getLong(SqlUtils.getCountSql(sql), params.toArray())) ;
            }
            
            pageIndex = (pageIndex < 0?1:pageIndex) ;
            result.setPageIndex(pageIndex) ; 
            
            // 支持分页
            sql = getLimitString(sql, params, pageIndex, pageSize) ;
        }

        ResultSet rs = null ;
        
        try {
	        rs = query(sql, params.toArray()) ;
	
	        List<String> columns = new ArrayList<String> () ;
	        
	        ResultSetMetaData meta = rs.getMetaData();
	        int numberOfColumns = meta.getColumnCount();
	
	        String[] properties = new String[numberOfColumns];
	        boolean[] timestampIndexs = new boolean[numberOfColumns];
	        int[] types = new int[numberOfColumns];
	
	        for (int i = 1; i <= numberOfColumns; i++) {
	        	final String columnName = meta.getColumnName(i).toLowerCase() ;
	            columns.add(columnName) ;
	            
	            if (clazzIsMap) {
	                properties[i - 1] = columnName ;
	            }
	            
	            types[i - 1] = getType(meta, i) ;
	
	        	timestampIndexs[i - 1] = (meta.getColumnType(i) == Types.TIMESTAMP) ;
	        }
	        
	        if (!clazzIsMap) {
	        	
		        for (Field field : clazz.getDeclaredFields()) {
		            String propertie = field.getName() ;
		
		            Column column = field.getAnnotation(Column.class) ;
		
		            int index ;
		            if (column != null) {
		                index = columns.indexOf(column.name()) ;
		            } else {
		                index = columns.indexOf(propertie) ;                
		            }
		            
		            if (index >= 0) {// 属性有对应的列
		                properties[index] = propertie ;
		                
		                Integer t = typesMap.get(field.getType()) ;
		                
		                if (t != null) {
		                	types[index] = t ;
		                }
		                
		                if (isFileObject) {
		                    if (field.getType().equals(java.io.File.class)) {
		                        fileFieldIndex = index ;
		                    }
		                }
		            }
		        }
	        }
	        
	        while(rs.next()) {
	            Object object = clazz.newInstance() ;
	            
	            @SuppressWarnings("unchecked")
				Map<String, Object> mapObject = clazzIsMap ? (Map<String, Object>) object : null ;
	            
	            for (int i = 0 ; i < properties.length ; i ++) {
	                String property = properties[i] ;
	                int type = types[i] ;
	                
	                if (property != null) {
	                    if (fileFieldIndex == i) {// 对blob特殊处理
	                        File f = new File(C.TEMP_DIR + "/"  + System.currentTimeMillis() + Math.random() + ".tmp") ;
	                        FileOutputStream fos = new FileOutputStream(f) ;
	                        InputStream is = rs.getBinaryStream(i + 1) ;
	
	                        byte[] buf = new byte[1024] ;
	                        while (is.read(buf) > 0) {
	                            fos.write(buf) ;                            
	                        }
	                        fos.close() ;
	                        
	                        if (clazzIsMap) {
	                        	mapObject.put(property, f) ;
	                        } else {
	                            BeanUtils.setProperty(object, property, f) ;
	                        } 
	                    } else {
	                        if (clazzIsMap) {
	                        	mapObject.put(property, getObject(rs, i, type)) ;
	                        } else {
	                            BeanUtils.setProperty(object, property, getObject(rs, i, type)) ;
	                        }
	                    }
	                }
	                
	            }
	
	            
	            result.add(object) ;
	       }
 
        } catch (BusinessException e) {
    		throw e;
    	} catch (Exception e) {
			log.error(e, e) ;
    		throw new BusinessException(e, "1000") ;
    	} finally {
        	if (rs != null) {
        	    try {
					rs.close() ;
				} catch (SQLException e) {
					log.error(e, e) ;
				}     
        	}
        }
        
        return result ;
    }
    
    private int getType(ResultSetMetaData meta, int i) throws Exception {
    	int colType = meta.getColumnType(i) ;
    	if (colType == Types.NUMERIC) {
        	if (meta.getScale(i) > 0) {
                return Types.DOUBLE;
        	} else {
        		int precision = meta.getPrecision(i) ;
        		if (precision <= 3) {
                    return Types.SMALLINT;
        		} else if (precision <= 10) {
                    return Types.INTEGER;
        		} else {
                    return Types.BIGINT;
        		}
        	}
        } else if (colType == Types.VARCHAR || colType == Types.CHAR ) {
            return Types.CHAR;
        } else if (colType == Types.TIMESTAMP ) {
            return colType;
        } else if (colType == Types.DATE ) {
            return colType;
        } else {
            return Types.JAVA_OBJECT;
        }
    }
    
    private Object getObject(ResultSet rs, int i, int type) throws Exception {
    	Object objValue = rs.getObject(i + 1);
		switch (type) {
		    case Types.CHAR: return objValue ;
		    case Types.JAVA_OBJECT: return objValue ;
		    case Types.DOUBLE: return objValue == null?objValue:rs.getDouble(i + 1) ;
		    case Types.INTEGER: return objValue == null?objValue:rs.getInt(i + 1) ;
		    case Types.BIGINT: return objValue == null?objValue:rs.getLong(i + 1) ;
		    case Types.SMALLINT: return objValue == null?objValue:rs.getShort(i + 1) ;
		    case Types.TIMESTAMP: return rs.getTimestamp(i + 1) ;
		    case Types.DATE: return rs.getDate(i + 1) ;
		    default: return objValue ;
		}
    }
    
    /*
    private void setObjectValue(boolean[] timestampIndexs, int i, boolean clazzIsMap) throws Exception {
    	if (timestampIndexs[i]) {
            if (clazzIsMap) {
            	mapObject.put(propertie, rs.getTimestamp(i + 1)) ;
            } else {
                BeanUtils.setProperty(object, propertie, rs.getTimestamp(i + 1)) ;
            }
    	} else {
            if (clazzIsMap) {
            	mapObject.put(propertie, rs.getObject(i + 1)) ;
            } else {
                BeanUtils.setProperty(object, propertie, rs.getObject(i + 1)) ;
            }
    	}
    }*/
    
    public long getMaxId(String tableName,String idName) throws Exception {
        return getLong(tableName, " max(" + idName + ") ", null, 0L) ;
    }

    public String getLimitString(final String sql, final List<Object> params, int pageIndex, int pageSize) {   

    	//CodeHelper.getInteger(tableName, columnName, id)
    	int type = SqlUtils.DB_TYPE_ORACLE ;
    	if (type == SqlUtils.DB_TYPE_ORACLE) {// oracle
	        params.add(pageIndex * pageSize) ;
	        params.add((pageIndex-1) * pageSize) ;
    	} else { // mysql
	        params.add((pageIndex-1) * pageSize) ;
	        params.add(pageSize) ;
    	}

        return SqlUtils.getLimitString(type, sql) ;
    } 

        
    public Long getNextValue(final String seqence) throws Exception {
    	String seqColName = null ;
    	if (seqence == null || "".equals(seqence)) {
    		seqColName = "common_seq" ;
    	} else {
    		seqColName = seqence;
    	}
    	
    	//sql = "select " + sql + ".nextval as seq from dual" ;
    	    	
    	//return ((BigDecimal)this.getRecord(sql, null).get("seq")).longValue() ;
        //return getLong(sql, null) ;
    	
    	return getLong("dual", seqColName + ".nextval", null, 0L) ;
    }

    /*
    public Map<String, Object> getRecord(final String sql, SQLGenerator.WHERE where) throws SQLException {
    	Map<String, Object> result = null ;

        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;
        
        if (where != null) {
	        for(int i = 0 ; i < where.params.size() ; i ++) {
	            statement.setObject(i + 1, where.params.get(i)) ;// 索引从1开始
	        }
        }
        
        ResultSet rs = statement.executeQuery() ;

        if (rs.next()) {
        	result = new HashMap<String, Object>() ;
        	ResultSetMetaData meta = rs.getMetaData() ;
            int numberOfColumns = meta.getColumnCount();
            for (int i = 1 ; i <= numberOfColumns ; i ++) {
            	result.put(meta.getColumnName(i).toLowerCase(), rs.getObject(i)) ;
            }
        }
        rs.close() ;
        statement.close() ;

        return result;
    }*/
    
    public ResultSetMetaData getMetaData(String sql) throws Exception {
    	        
        return query("select * from (" + sql + ") a where 1=0", null).getMetaData() ;
    }


    public int executeSQL(String sql, Object criterion) throws Exception {
        //SQLGenerator generator = new SQLGenerator() ;
    	SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
        
        SQLGenerator.UPDATE update = new SQLGenerator.UPDATE() ;
        update.sql.append(sql) ;
        
        return this.update(update, generator.getWhere()) ;
    } 
    
    


    public int executeName(String name, Object value) throws Exception {
    	
		SqlParser parser = paserSQL(name, value);

		return execute(parser.parse(), parser.getParams().toArray()) ;
    }

	private SqlParser paserSQL(String name, Object value) throws Exception {
		String[] names = name.split(":") ;
    	
    	SqlBeanInf sqlBean = (SqlBeanInf)CodeHelper.getAppContext().getBean(names[0]);
    	
    	if (sqlBean == null) {
    		throw new Exception("not SQLs's bean found!") ;
    	}
    	String sql = sqlBean.get(names[1]);

    	SqlParser parser = new SqlParser(sql, value) ;
		return parser;
	} 
    
	public ArrayPageList<?> queryName(String name, Object value, Class<?> resultClazz) throws Exception {
		return queryName(name, value, null, null, resultClazz) ;
	}


    public ArrayPageList<?> queryName(
    		String name, 
    		Object value, 
    		BaseSO so, 
    		Class<?> resultClazz) 
    throws Exception {
    	return queryName(name, value, so, so.getOrderList(), resultClazz) ;
    }

    /**
     * 
     * 
     * pls use {@link #queryName(String, Object, BaseSO, Class)}
     * 
     * @param name
     * @param value
     * @param criterion
     * @param orderList
     * @param resultClazz
     * @return
     * @throws Exception
     */
    private ArrayPageList<?> queryName(
    		String name, 
    		Object value, 
    		Object criterion, 
    		ArrayOrderList orderList, 
    		Class<?> resultClazz) 
    throws Exception {
    	
		SqlParser parser = paserSQL(name, value);

		String sql = parser.parse() ;
		List<Object> params = parser.getParams() ;
		
		Integer index = null ;
		Integer size = null ;
		if (value instanceof BaseSO) {
			index = ((BaseSO)value).getPageIndex() ;
			size = ((BaseSO)value).getPageSize() ;
		}
		
		if (criterion != null) {
			//SQLGenerator g = new SQLGenerator() ;
			SQLGenerator generator = generatorAdapter.getInstance(criterion) ;
			SQLGenerator.WHERE w = generator.getWhere() ;
			sql += w.sql ;
			if (orderList != null) {
				sql += orderList.toOrderString() ;
			}
			params.addAll(w.params) ;
		}
		
        return query(new StringBuffer(sql), params, index, size, resultClazz, false) ;
    } 

    public Object findName(String name, Object value, Class<?> resultClazz) throws Exception {
    	ArrayPageList<?> list = queryName(name, value, resultClazz) ;
    	
    	if (list.size() > 0) {
    		return list.get(0) ;
    	}
    	
        return null ;
    } 
    

    public Long getName(String name, Object value, Long defaultValue) throws Exception {
    	
		SqlParser parser = paserSQL(name, value);

		//String sql = parser.parse() ;
		//List<Object> params = parser.getParams() ;
		
        Long result = getLong(parser.parse(), parser.getParams().toArray()) ;

        return (result == null?defaultValue:result) ;
        
        /*
        PreparedStatement statement = this.getConnection().prepareStatement(sql) ;
        
        for(int i = 0 ; i < params.size() ; i ++) {
            statement.setObject(i + 1, params.get(i)) ;// 索引从1开始
        }
        
        ResultSet rs = statement.executeQuery() ;

        if (rs.next()) {
            result = rs.getLong(1) ;
        }
        rs.close() ;
        statement.close() ;

        return result;*/
    } 
    
    
}
